﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common.Logging;

namespace InfoboardAdapterC.Common
{
    public class SqlServerDal
    {
        private static readonly ILog _loggerLog = LogManager.GetLogger<SqlServerDal>();
        /// <summary>
        /// 根据sql获取DataTable表
        /// </summary>
        /// <param name="text"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string text, string connection)
        {
            var sql = string.Format(text);
            var dt = Execute_SQL_With_DT(sql, connection);
            if (dt == null || dt.Rows.Count == 0 || dt.Columns.Count == 0)
            {
                throw new Exception("查询语句查询后结果为null或表数据为0");
            }
            return dt;
        }

        /// <summary>
        /// 获取sql查询结果首字段的集合
        /// </summary>
        /// <param name="text"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        public List<string> GetResult(string text,string connection)
        {
            var content = new List<string>();
            var sql = string.Format(text);
            var dt = Execute_SQL_With_DT(sql,connection);
            if (dt == null || dt.Rows.Count == 0 || dt.Columns.Count == 0)
                return content;

            for (var i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][0] != DBNull.Value)
                {
                    content.Add(dt.Rows[i][0].ToString());
                }
            }
            return content;
        }
        private static DataTable Execute_SQL_With_DT(string sqlString,string connection)
        {
            try
            {
                var ds = new DataSet();
                var conn = new SqlConnection(connection);
                conn.Open();
                var cmd = new SqlCommand(sqlString, conn);
                var da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                _loggerLog.Error("执行返回DataSet结果的SQL语句：" + sqlString + "\r\n产生异常：" + ex.Message);
            }
            return null;
        }
    }
}
